#!/usr/bin/env python
# encoding:utf-8
# Author: jixingxing
# ORM  之  创建表
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

"""
Base.metadata.create_all(engine)  #最后执行,将所有的Base的子类,根据子类,将所有的表创建出来
"""

# 1、首先创建引擎
engine = create_engine("mysql+pymysql://testuser:testuserpasswd@112.74.209.145:3306/test1", max_overflow=5)

# engine.execute('create table tb3 (`id` int(4),`name` CHAR(40)) engine=MyISAM  default charset=utf8;')

# 2、获取一个Base对象,必须要这么做
Base = declarative_base()


# 3、创建单表
class Users(Base):  # 必须继承Base
    __tablename__ = 'users'  # 表名
    id = Column(Integer, primary_key=True)  # 创建id列,整数形式,是主键
    name = Column(String(32))  # 创建name列,最大32个字节
    extra = Column(String(16))

    __table_args__ = (
        UniqueConstraint('id', 'name', name='uix_id_name'),
        Index('ix_id_name', 'name', 'extra')
    )


# 4、一对多 (外键)
class Favor(Base):
    __tablename__ = 'favor'

    nid = Column(Integer, primary_key=True)
    caption = Column(String(50), default='red', unique=True)


class Person(Base):
    __tablename__ = 'person'
    nid = Column(Integer, primary_key=True)
    name = Column(String(32), index=True, nullable=True)
    favor_id = Column(Integer, ForeignKey("favor.nid"))


# 5、多对多

class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True)
    name = Column(String(64), unique=True, nullable=False)
    port = Column(Integer, default=22)


class Server(Base):
    __tablename__ = 'server'
    id = Column(Integer, primary_key=True, autoincrement=True)  # 自动增量
    hostname = Column(String(64), unique=True, nullable=False)


class ServerToGroup(Base):
    __tablename__ = 'servertogroup'
    nid = Column(Integer, primary_key=True, autoincrement=True)
    server_id = Column(Integer, ForeignKey('server.id'))
    grooup_id = Column(Integer, ForeignKey('group.id'))


# 6、写到数据库中
def init_db():
    Base.metadata.create_all(engine)


# 7、从数据库中删除
def drop_db():
    Base.metadata.drop_all(engine)


# init_db()
drop_db()
